﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using OgilvyOne.IDAL;
using OgilvyOne.Model;

namespace OgilvyOne.SqlServerDAL
{
    public class CmsArticleFront : ICmsArticleFront
    {

        private const string GET_ARTICLE_SQL = "select articleid,columnid,title,content,addtime,starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime from cms_article_front where articleid=@articleid";
        public CmsArticleModel getCmsArticle(int aid)
        {
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@articleid", SqlDbType.Int, 4)
            };
            parms[0].Value = aid;
            CmsArticleModel model = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_ARTICLE_SQL, parms))
            {
                if (rdr.Read())
                {
                    model = new CmsArticleModel();
                    model.Articleid = rdr.GetInt32(0);
                    model.Columnid = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) model.Title = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) model.Content = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) model.Addtime = rdr.GetDateTime(4);
                    if (!rdr.IsDBNull(5)) model.Starttime = rdr.GetDateTime(5);
                    if (!rdr.IsDBNull(6)) model.Endtime = rdr.GetDateTime(6);
                    if (!rdr.IsDBNull(7)) model.Articletype = rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) model.Titleimg = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) model.Author = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) model.Authoremail = rdr.GetString(10);
                    if (!rdr.IsDBNull(11)) model.Indextop = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) model.Sorttop = rdr.GetString(12);
                    if (!rdr.IsDBNull(13)) model.Istouch = rdr.GetString(13);
                    if (!rdr.IsDBNull(14)) model.Templateid = rdr.GetInt32(14);
                    if (!rdr.IsDBNull(15)) model.Summary = rdr.GetString(15);
                    if (!rdr.IsDBNull(16)) model.Source = rdr.GetString(16);
                    if (!rdr.IsDBNull(17)) model.Sourceurl = rdr.GetString(17);
                    if (!rdr.IsDBNull(18)) model.Linkpath = rdr.GetString(18);
                    if (!rdr.IsDBNull(19)) model.Tag = rdr.GetString(19);
                    if (!rdr.IsDBNull(20)) model.Articletitle = rdr.GetString(20);
                    if (!rdr.IsDBNull(21)) model.Articlekeyword = rdr.GetString(21);
                    if (!rdr.IsDBNull(22)) model.Articledesc = rdr.GetString(22);
                    if (!rdr.IsDBNull(23)) model.Isstaticpub = rdr.GetString(23);
                    if (!rdr.IsDBNull(24)) model.Staticpagename = rdr.GetString(24);
                    
                }
            }
            return model;
        }

        public bool Del(string ids)
        {
            bool re = false;
            string delsql = "delete from cms_article_front where articleid in(" + ids + ")";
            int i = SQLHelper.ExecuteSql(delsql, new SqlParameter[] { });
            if (i > 0) re = true;
            return re;
        }

        public bool PushFront(CmsArticleModel cm)
        {
            if (cm.Articleid < 1) return false;
            if (IsExists(cm.Articleid))
            {//更新
                return Update(cm);
            }
            else
            {//新增
                return Insert(cm);
            }
        }

        public bool Insert(CmsArticleModel article)
        {
            bool re = false;
            string insertsql = "insert into cms_article_front(articleid,columnid,title,content,addtime,starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,modifytime,hits,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename) "
                + "values("+article.Articleid+"," + article.Columnid + ",@title,@content,getDate()," + (article.Starttime.Year < 1900 ? "null" : "'" + article.Starttime.ToString("yyyy-MM-dd") + "'") + "," + (article.Endtime.Year < 1900 ? "null" : "'" + article.Endtime.ToString("yyyy-MM-dd") + "'")
                + ",@articletype,@titleimg,@author,@authoremail,@indextop,@sorttop,@istouch,@templateid,@summary,@source,@sourceurl,@linkpath,@tag,@modifytime,0,@articletitle,@articlekeyword,@articledesc,@isstaticpub,@staticpagename)";
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@title", SqlDbType.VarChar, 255),
                new SqlParameter("@content", SqlDbType.Text),
                new SqlParameter("@articletype", SqlDbType.Char,1),
                new SqlParameter("@titleimg",SqlDbType.VarChar,255),
                new SqlParameter("@author", SqlDbType.VarChar,50),
                new SqlParameter("@authoremail", SqlDbType.VarChar, 255),
                new SqlParameter("@indextop",SqlDbType.Char,1),
                new SqlParameter("@sorttop", SqlDbType.Char,1),
                new SqlParameter("@istouch", SqlDbType.Char,1),
                new SqlParameter("@templateid", SqlDbType.Int,4),
                new SqlParameter("@summary", SqlDbType.VarChar,500),
                new SqlParameter("@source", SqlDbType.VarChar,255),
                new SqlParameter("@sourceurl", SqlDbType.VarChar, 255),
                new SqlParameter("@linkpath", SqlDbType.VarChar, 100),
                new SqlParameter("@tag", SqlDbType.VarChar, 200),
                new SqlParameter("@modifytime", SqlDbType.DateTime),
                new SqlParameter("@articletitle", SqlDbType.VarChar, 255),
                new SqlParameter("@articlekeyword",SqlDbType.VarChar, 500),
                new SqlParameter("@articledesc", SqlDbType.VarChar, 500),
                new SqlParameter("@isstaticpub", SqlDbType.Char, 1),
                new SqlParameter("@staticpagename", SqlDbType.VarChar, 100)
            };
            parms[0].Value = article.Title;
            parms[1].Value = article.Content;
            parms[2].Value = article.Articletype;
            parms[3].Value = article.Titleimg;
            parms[4].Value = article.Author;
            parms[5].Value = article.Authoremail;
            parms[6].Value = article.Indextop;
            parms[7].Value = article.Sorttop;
            parms[8].Value = article.Istouch;
            parms[9].Value = article.Templateid;
            parms[10].Value = article.Summary;
            parms[11].Value = article.Source;
            parms[12].Value = article.Sourceurl;
            parms[13].Value = article.Linkpath;
            parms[14].Value = article.Tag;
            parms[15].Value = DateTime.Now;
            parms[16].Value = article.Articletitle;
            parms[17].Value = article.Articlekeyword;
            parms[18].Value = article.Articledesc;
            parms[19].Value = article.Isstaticpub;
            parms[20].Value = article.Staticpagename;
            int i = SQLHelper.ExecuteSql(insertsql, parms);
            if (i > 0) re = true;
            return re;
        }

        public bool Update(CmsArticleModel article)
        {
            bool re = false;
            string updatesql = "update cms_article_front set title=@title,content=@content,starttime=" + (article.Starttime.Year < 1900 ? "null" : "'" + article.Starttime.ToString("yyyy-MM-dd") + "'") + ""
                + ",endtime=" + (article.Endtime.Year < 1900 ? "null" : "'" + article.Endtime.ToString("yyyy-MM-dd") + "'") + ",articletype=@articletype,titleimg=@titleimg,author=@author,authoremail=@authoremail,indextop=@indextop,sorttop=@sorttop,istouch=@istouch,templateid=@templateid,summary=@summary,"
                + "source=@source,sourceurl=@sourceurl,linkpath=@linkpath,tag=@tag,modifytime=getDate(),articletitle=@articletitle,articlekeyword=@articlekeyword,articledesc=@articledesc,isstaticpub=@isstaticpub,staticpagename=@staticpagename where articleid=" + article.Articleid;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@title", SqlDbType.VarChar, 255),
                new SqlParameter("@content", SqlDbType.Text),
                new SqlParameter("@articletype", SqlDbType.Char,1),
                new SqlParameter("@titleimg",SqlDbType.VarChar,255),
                new SqlParameter("@author", SqlDbType.VarChar,50),
                new SqlParameter("@authoremail", SqlDbType.VarChar, 255),
                new SqlParameter("@indextop",SqlDbType.Char,1),
                new SqlParameter("@sorttop", SqlDbType.Char,1),
                new SqlParameter("@istouch", SqlDbType.Char,1),
                new SqlParameter("@templateid", SqlDbType.Int,4),
                new SqlParameter("@summary", SqlDbType.VarChar,500),
                new SqlParameter("@source", SqlDbType.VarChar,255),
                new SqlParameter("@sourceurl", SqlDbType.VarChar, 255),
                new SqlParameter("@linkpath", SqlDbType.VarChar, 100),
                new SqlParameter("@tag", SqlDbType.VarChar, 200),
                new SqlParameter("@articletitle", SqlDbType.VarChar, 255),
                new SqlParameter("@articlekeyword",SqlDbType.VarChar, 500),
                new SqlParameter("@articledesc", SqlDbType.VarChar, 500),
                new SqlParameter("@isstaticpub", SqlDbType.Char, 1),
                new SqlParameter("@staticpagename", SqlDbType.VarChar, 100)
            };
            parms[0].Value = article.Title;
            parms[1].Value = article.Content;
            parms[2].Value = article.Articletype;
            parms[3].Value = article.Titleimg;
            parms[4].Value = article.Author;
            parms[5].Value = article.Authoremail;
            parms[6].Value = article.Indextop;
            parms[7].Value = article.Sorttop;
            parms[8].Value = article.Istouch;
            parms[9].Value = article.Templateid;
            parms[10].Value = article.Summary;
            parms[11].Value = article.Source;
            parms[12].Value = article.Sourceurl;
            parms[13].Value = article.Linkpath;
            parms[14].Value = article.Tag;
            parms[15].Value = article.Articletitle;
            parms[16].Value = article.Articlekeyword;
            parms[17].Value = article.Articledesc;
            parms[18].Value = article.Isstaticpub;
            parms[19].Value = article.Staticpagename;
            int i = SQLHelper.ExecuteSql(updatesql, parms);
            if (i > 0) re = true;
            return re;
        }

        /// <summary>
        /// 文章是否存在
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        private bool IsExists(int aid)
        {
            string sql = "select articleid from cms_article_front where articleid=@articleid";
            bool re = false;
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@articleid", SqlDbType.Int, 4)
            };
            parms[0].Value = aid;
            CmsArticleModel model = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                if (rdr.Read())
                {
                    re = true;
                }
            }
            return re;
        }

        public DataSet GetSqlTable(string sql)
        {
            
            return SQLHelper.Query(sql);
        }

    }
}
